package com.lepu.pcecg500.util;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Environment;
import android.util.Log;
import com.blankj.utilcode.util.LogUtils;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

/**
 * author : xiaoxin
 * date   : 2020/10/18 15:04
 * desc   :
 * update :
 */
public class GwtjDbUtil {

    private static SQLiteDatabase INSTANCE;

    public static SQLiteDatabase getInstance(){
        if (INSTANCE == null) {
            INSTANCE = getDatabase();
        }
        return INSTANCE;
    }

    public static SQLiteDatabase getDatabase() {
        return SQLiteDatabase.openOrCreateDatabase(getInnerSDCardPath() + Constant.DATABASE_NAME1, null);
    }

    public static SQLiteDatabase getDatabaseInfo() {
        return SQLiteDatabase.openOrCreateDatabase(getInnerSDCardPath() + Constant.DATABASE_NAME, null);
    }

    /**
     * 拷贝数据库文件
     * @param context 上下文
     * @param db 数据库文件名
     */
    public static void copyAddressDB(final Context context,final String path,final String db) {
        File filePath = new File(path);
        File file = new File(path,db);
        if (!filePath.exists()) {
            filePath.mkdir();
        }
        if(file.exists() && file.length() > 0) {
            LogUtils.e( "数据库存在无需拷贝");
        }else {
            new Thread() {
                public void run() {
                    //把assets里面的数据库文件拷贝到手机系统里面
                    try {
                        InputStream is = context.getAssets().open(db);
                        File file = new File(path,db);
                        FileOutputStream fos = new FileOutputStream(file);

                        LogUtils.e("数据库路径地址：" + path);
                        byte[] b = new byte[1024];
                        int len = -1;
                        while((len = is.read(b))!=-1) {
                            fos.write(b, 0, len);
                        }
                        is.close();
                        fos.close();
                        LogUtils.e( "拷贝成功");
                    } catch (IOException e) {
                        e.printStackTrace();
                        LogUtils.e(e.getMessage());
                    }
                }
            }.start();
        }
    }

    public static String getInnerSDCardPath(){
        return Environment.getExternalStorageDirectory() +  Constant.DB_PATH;
    }

    /**
     * 查找方法
     * 返回的是一个Cursor对象
     * selectionArgs 查询条件占位符
     */
    public static Cursor selectSQL(SQLiteDatabase db, String sql, String[] selectionArgs){
        Cursor cursor = null;
        if (db != null){
            cursor = db.rawQuery(sql,selectionArgs);
        }
        return cursor;
    }

    public static void execSQL(SQLiteDatabase db, String sql){
        if (db!=null) {
            if (sql != null && !"".equals(sql)) {
                db.execSQL(sql);
            }
        }
    }

    /**
     *
     * @param state 0成功 1失败
     * @param code
     * @param content
     */
    public static void selectDB(int state,String code,String content) {
        //查询数据库
        int abc = 0;
        int i = 0;
        Date date = new Date();
        boolean isData = false;
        Calendar calendar = Calendar.getInstance();
        int year = calendar.get(Calendar.YEAR);
        int month = calendar.get(Calendar.MONTH)+1;
        int day = calendar.get(Calendar.DAY_OF_MONTH);
        String times = year+"-"+month+"-"+day;
        Date mDate = null;
        try {
            mDate = new SimpleDateFormat("yyyy-MM-dd").parse(times);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        times = new SimpleDateFormat("yyyy-MM-dd").format(mDate);

        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");

        String sim = dateFormat.format(date);

        Log.i("md", "时间sim为： " + sim);

        SQLiteDatabase db = getDatabase();
        String selectSql = "select * from " + Constant.TABLE_NAME+" where Code='"+code+"' and addTime='"+times+"'";
        Cursor cursor = selectSQL(db, selectSql, null);//查询结果用cursor类型数据存储
        if (cursor.getCount() == 0) {
            i = 1;
        }
        while (cursor.moveToNext()) {
            isData = true;
            i = cursor.getCount() + 1;
            int id = cursor.getInt(cursor.getColumnIndex("id"));

            abc++;
            if (abc > 1) {
                String Code = cursor.getString(cursor.getColumnIndex("Code"));

                String Content = cursor.getString(cursor.getColumnIndex("Content"));

                String State = cursor.getString(cursor.getColumnIndex("State"));

                String Type = cursor.getString(cursor.getColumnIndex("Type"));

                String addDate = cursor.getString(cursor.getColumnIndex("addDate"));
                String addTime = cursor.getString(cursor.getColumnIndex("addTime"));

                Log.e("XueTangActivity", "id:" + id + "--Content:" + Content + "--State:" + State + "--Type:" + Type
                        + "--addDate:" + addDate);
            }
            Log.e("XueTangActivity", "数据库查询了");
        }

        Log.e("XueTangActivity", "日期time:"+times);
        //获取到数据插入数据库
        String sql = "insert into " + Constant.TABLE_NAME + "(code,content,state,type,addData,addTime)"
                + " values (" + ",'" + code + "','" + content + "','" + state + "','" + 0 + "','" + sim +"','"+times+"')";
        if(isData){
            sql = "update "+Constant.TABLE_NAME+" set Content='"+content+"',State='"+state+"' where Code='"+code+"'";
        }
        Log.e("XueTangActivity", "SQL:" + sql);
        execSQL(db, sql);//执行语句
        db.close();
        Log.e("XueTangActivity", "记录保存成功:");
    }

    public static List<GwtjDbBean> query(String tableName) {

        //查询全部数据
        Cursor cursor = getInstance().query("TP_DATA2",null,null,null,null,null,null,null);
        List<GwtjDbBean> list = new ArrayList<>();
        if(cursor.getCount() > 0) {
            //移动到首位
            cursor.moveToFirst();
            for (int i = 0; i < cursor.getCount(); i++) {
                int id = cursor.getInt(cursor.getColumnIndex("id"));
                String code = cursor.getString(cursor.getColumnIndex(Constant.Code));
                String state = cursor.getString(cursor.getColumnIndex(Constant.State));
                String type = cursor.getString(cursor.getColumnIndex(Constant.Type));
                String content = cursor.getString(cursor.getColumnIndex(Constant.Content));
                String addTime = cursor.getString(cursor.getColumnIndex(Constant.ADDTime));
                String addDate = cursor.getString(cursor.getColumnIndex(Constant.ADDDATE));


                GwtjDbBean model = new GwtjDbBean();
                model.setId(id);
                model.setCode(code);
                model.setState(state);
                model.setType(type);
                model.setAddTime(addTime);
                model.setAddData(addDate);
                model.setContent(content);
                model.setTableName(tableName);

                model.setContent2(cursor.getString(cursor.getColumnIndex(Constant.Content2)));
                model.setContent3(cursor.getString(cursor.getColumnIndex(Constant.Content3)));

                list.add(model);
                //移动到下一位
                cursor.moveToNext();
            }
        }

        cursor.close();
        return list;
    }

    public static List<GwtjDbBean> query1(String tableName) {

        //查询全部数据
        Cursor cursor = getDatabaseInfo().query("TP_DATA2",null,null,null,null,null,null,null);
        List<GwtjDbBean> list = new ArrayList<>();
        if(cursor.getCount() > 0) {
            //移动到首位
            cursor.moveToFirst();
            for (int i = 0; i < cursor.getCount(); i++) {
                int id = cursor.getInt(cursor.getColumnIndex("id"));
                String code = cursor.getString(cursor.getColumnIndex(Constant.Code));
                String state = cursor.getString(cursor.getColumnIndex(Constant.State));
                String type = cursor.getString(cursor.getColumnIndex(Constant.Type));
                String content = cursor.getString(cursor.getColumnIndex(Constant.Content));
                String addTime = cursor.getString(cursor.getColumnIndex(Constant.ADDTime));
                String addDate = cursor.getString(cursor.getColumnIndex(Constant.ADDDATE));


                GwtjDbBean model = new GwtjDbBean();
                model.setId(id);
                model.setCode(code);
                model.setState(state);
                model.setType(type);
                model.setAddTime(addTime);
                model.setAddData(addDate);
                model.setContent(content);
                model.setTableName(tableName);

                model.setContent2(cursor.getString(cursor.getColumnIndex(Constant.Content2)));
                model.setContent3(cursor.getString(cursor.getColumnIndex(Constant.Content3)));

                list.add(model);
                //移动到下一位
                cursor.moveToNext();
            }
        }

        cursor.close();
        return list;
    }

    public static void insertDb(List<GwtjDbBean> gwtjDbBeanList) {
        SQLiteDatabase database = getDatabaseInfo();
        database.beginTransaction();//开始事物

        for (int i = 0; i < gwtjDbBeanList.size(); i++) {
            GwtjDbBean gwtjDbBean = gwtjDbBeanList.get(i);
            ContentValues values = new ContentValues();
            values.put(Constant.Code, gwtjDbBean.getCode());
            values.put(Constant.Content, gwtjDbBean.getContent());
            values.put(Constant.Content2, gwtjDbBean.getContent2());
            values.put(Constant.Content3, gwtjDbBean.getContent3());
            values.put(Constant.State, gwtjDbBean.getState());
            values.put(Constant.Type, gwtjDbBean.getType());
            values.put(Constant.ADDDATE, gwtjDbBean.getAddData());
            values.put(Constant.ADDTime, gwtjDbBean.getAddTime());
            database.insert(gwtjDbBean.getTableName(), null, values);
            values.clear();
        }
        database.setTransactionSuccessful();//事务已经执行成功
        database.endTransaction();//结束事务
        LogUtils.e("完成");
    }

    /**
     * 检查表是否存在某个字段
     * @param tableName  表名
     * @param columnName 列名
     * @return
     */
    public static boolean checkColumnExists(SQLiteDatabase db, String tableName, String columnName) {
        boolean result = false;
        Cursor cursor = null;

        try {
            cursor = db.rawQuery("select * from sqlite_master where name = ? and sql like ?",
                    new String[]{tableName, "%" + columnName + "%"});
            result = null != cursor && cursor.moveToFirst();
        } catch (Exception e) {
            Log.e("tag", "checkColumnExists2..." + e.getMessage());
        } finally {
            if (null != cursor && !cursor.isClosed()) {
                cursor.close();
            }
        }

        return result;
    }

    /**
     * 添加列
     */
    public static void addColumnByBySQL(SQLiteDatabase db, String tableName, String columnName) {
        try {
            boolean checkColumnExists = checkColumnExists(db, tableName, columnName);
            if (!checkColumnExists) {
                String addColumnSQL = "alter table " + tableName + " add column " + columnName + " varchar(255)";
                db.execSQL(addColumnSQL);
                Log.d("addColumnByBySQL", addColumnSQL);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     *  添加心电图数据
     * @param gwtjDbBean
     */
    public static void insertEcgDb(GwtjDbBean gwtjDbBean) {
        SQLiteDatabase database = getDatabaseInfo();
        String selectSql = "select * from " + Constant.TABLE_ECG+" where Code='"+gwtjDbBean.getCode()
                +"' and addTime='"+gwtjDbBean.getAddTime()+"'";
        Cursor cursor = selectSQL(database, selectSql, null);//查询结果用cursor类型数据存储

//        database.beginTransaction();//开始事物
        if (cursor.getCount() > 0) {
            String sql = "update "+Constant.TABLE_ECG + " set hr='"+gwtjDbBean.getHr() +
                    "',conclusion='"+gwtjDbBean.getConclusion()
                    + "',path='"+gwtjDbBean.getPath()
                     + "',State='"+gwtjDbBean.getState()
                    +"' where Code='"+gwtjDbBean.getCode()+"'";
            execSQL(database, sql);//执行语句
        } else {
            ContentValues values = new ContentValues();
            values.put(Constant.Code, gwtjDbBean.getCode());
            values.put(Constant.hr, gwtjDbBean.getHr());
            values.put(Constant.conclusion, gwtjDbBean.getConclusion());
            values.put(Constant.path, gwtjDbBean.getPath());
            values.put(Constant.State, gwtjDbBean.getState());
            values.put(Constant.Type, gwtjDbBean.getType());
            values.put(Constant.ADDDATE, gwtjDbBean.getAddData());
            values.put(Constant.ADDTime, gwtjDbBean.getAddTime());
            database.insert(gwtjDbBean.getTableName(), null, values);
//        values.clear();
//        database.setTransactionSuccessful();//事务已经执行成功
//        database.endTransaction();//结束事务
            LogUtils.e("完成");
        }

    }
}
